#! /bin/bash
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists oe_dwb;
-- 赵宗尧
drop table if exists oe_dwb.dwb_clock_late_detail;
create table if not exists oe_dwb.dwb_clock_late_detail(
    --班级课表
    class_id string comment '班级id',
    class_date string comment '上课时间 "上课时间段"',
    content string comment '课程内容',
    --班级上课时间表
    morning_begin_time string comment '上午开始时间 "有效时间段"',
    morning_end_time string comment '上午结束时间',
    afternoon_begin_time string comment '下午开始时间',
    afternoon_end_time string comment '下午结束时间',
    evening_begin_time string comment '晚上开始时间',
    evening_end_time string comment '晚上结束时间',
    --学生打卡记录表
    student_id string comment '学员id',
    signin_time string comment '签到时间 "是否打卡"',
    share_state bigint comment '判断是否在听课 "待议"',

    dt string comment '记录'
)comment '打卡迟到宽表'
    row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'Snappy');

drop table if exists oe_dwb.dwb_leave_late_detail;
create table if not exists oe_dwb.dwb_leave_late_detail(
    --班级课表
    class_id string comment '班级id',
    class_date string comment '上课时间 "上课时间段"',
    content string comment '课程内容',
    --班级上课时间表
    morning_begin_time string comment '上午开始时间 "有效时间段"',
    morning_end_time string comment '上午结束时间',
    afternoon_begin_time string comment '下午开始时间',
    afternoon_end_time string comment '下午结束时间',
    evening_begin_time string comment '晚上开始时间',
    evening_end_time string comment '晚上结束时间',
    --学生请假申请表
    student_id string comment '学员id',
    audit_state bigint comment '审核状态 "是否通过审核"',
    begin_time string comment '请假开始时间',
    begin_time_type bigint comment '请假结束时间段',
    end_time string comment '请假结束时间',
    end_time_type bigint comment '请假结束时间段',
    valid_state bigint comment '是否有效',

    dt string comment '记录'
)comment '请假迟到宽表'
    row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'Snappy');
"

PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
-- 赵宗尧
insert into oe_dwb.dwb_clock_late_detail
select
    --班级课表
    a.class_id,
    a.class_date,
    a.content,
    --班级上课时间表
    b.morning_begin_time,
    b.morning_end_time,
    b.afternoon_begin_time,
    b.afternoon_end_time,
    b.evening_begin_time,
    b.evening_end_time,
    --学生打卡记录表
    c.student_id,
    c.signin_time,
    c.share_state,

    '2024-08-22' as dt
from oe_dwd.dim_course_table_upload_detail a
left join oe_dwd.dim_class_time_table b
    on a.class_id = b.class_id
    and a.class_date between b.use_begin_date and b.use_end_date
left join oe_dwd.fact_student_signin_record c
    on a.class_id = c.class_id
    and a.class_date = c.signin_date
--     and b.id = c.time_table_id
order by class_id,class_date
;
-- 董曲岩
insert into oe_dwb.dwb_leave_late_detail
select
    --班级课表
    a.class_id,
    a.class_date,
    a.content,
    --班级上课时间表
    b.morning_begin_time,
    b.morning_end_time,
    b.afternoon_begin_time,
    b.afternoon_end_time,
    b.evening_begin_time,
    b.evening_end_time,
    --学生请假申请表
    d.student_id,
    d.audit_state,
    d.begin_time,
    d.begin_time_type,
    d.end_time,
    d.end_time_type,
    d.valid_state,

    '2024-08-22' as dt
from oe_dwd.dim_course_table_upload_detail a
left join oe_dwd.dim_class_time_table b
    on a.class_id = b.class_id
    and a.class_date between b.use_begin_date and b.use_end_date
left join oe_dwd.fact_student_leave_apply d
    on a.class_id = d.class_id
    and a.class_date between substring(d.begin_time,1,10) and substring(d.end_time,1,10)
where valid_state = 1 and audit_state != 2
;
"